﻿<cfcomponent name="data" ExtDirect="true">
	
<cffunction name="GetAll" ExtDirect="true">
					
	<cfargument name="page" >
	<cfargument name="start" >
	<cfargument name="limit" >
	<cfargument name="sort" >
	<cfargument name="filter" >
	<cfargument name="group" >
	<cfargument name="departmentcode" >
	
	
	
<!--- Temp to be replaced during patching --->
<cfset client.company_dsn = 'FBC_CBOSE' >
<cfset client.global_dsn = 'IBOSE_GLOBAL' >
<cfset client.companyname = 'Filinvest Baseline company' />
<cfset client.companycode = 'FBC' />
<!---<CFMODULE TEMPLATE="../../../common/autogenfield.cfm" ---> 
<cfset autogenpath = "../../../../ibosesvn/common/autogenfield.cfm"  > 

<!---End  Temp to be replaced during patching --->
			
	  <cfset var qryAuthors = "" /> 
	  
	  <cfquery name="qryMRF" datasource="IBOSE_GLOBAL">
	    SELECT  FIRSTNAME,
	            LASTNAME,
	            MIDDLENAME,
	            REFERENCECODE,
	            DATEOFAPPLICATION,
	            POSITIONFIRSTPRIORITY,
	            POSITIONSECONDPRIORITY,
	            POSITIONTHIRDPRIORITY,
	            EXPECTEDSALARY,
	            CURRENTSALARY,
	            AGE,
	            GENDER,
	            CIVILSTATUS,
	            CITIZENSHIP,
	            COLLEGESCHOOL,
	            COLLEGECOURSE,
	            COLLEGEISGRAD,
	            POSTGRADSCHOOL,
	            POSTGRADCOURSE,
	            POSTGRADISGRAD,
	            VOCATIONALSCHOOL,
	            VOCATIONALCOURSE,
	            VOCATIONALISGRAD,
	            PRESENTADDRESSPOSTAL,
	            COMPANYCODE
	            
	     FROM  GRGPOOL
	     	                       
	      
	 <cfset where             = "()" >
	 <cfif isdefined('query')>
	   WHERE nameofemployee LIKE '%#query#%' <!--- reserved for grid with search plugin --->
	 <cfelse>
	 	 <cfset where             = " (" >
            <cfset tmpdatafield      = "" >
            <cfset tmpfilteroperator = "0" >
			
			<cftry>
			<cfset filter = deserializejson(filter) >	<!---Deserialize JSON string coz Router forgets to do the work on filter but not on sort--->
			<cfloop array=#filter# index="filterdata">
            	<cftry>
					<cfset filterdatafield = filterdata.field />
					<cfcatch>
						<cfbreak>
					</cfcatch>
				</cftry>
            
            	<cfset filterdatafield = filterdata.field />
				<cfset filtervalue     = filterdata.value />
				<cfset filtertype      = filterdata.type />
				<cfif tmpdatafield EQ "" >
                <cfset tmpdatafield = filterdatafield >	
                <cfelseif tmpdatafield NEQ filterdatafield >
                	<cfset where = "#where# ) AND ( " >
                <cfelseif tmpdatafield EQ filterdatafield >
                	<cfif tmpfilteroperator EQ 0>
                    	<cfset where = "#where# AND " >
                    <cfelse>
                    	<cfset where = "#where# OR " >
                    </cfif>
				</cfif>
                
                <cfif ucase(filtertype) EQ "STRING" >
					<cfset where = "#where##filterdatafield#  LIKE '%#filtervalue#%'" >
				<cfelseif  ucase(filtertype) EQ "NUMERIC" >
					<cfset filtercondition = filterdata.comparison >
					<cfset expression = "#Ucase(Trim(filtercondition))#" >
               			<cfif expression  EQ "LT">
						   	<cfset where = "#where##filterdatafield#  < #filtervalue#">
						<cfelseif expression EQ "GT"> 	   
							<cfset where = "#where##filterdatafield#  > #filtervalue#">
						<cfelseif expression EQ "EQ"> 	   	
							<cfset where = "#where##filterdatafield#  = #filtervalue#">
						<cfelse>
					</cfif>
				<cfelseif  ucase(filtertype) EQ "DATE" >
					<cfset filtercondition = filterdata.comparison >
					<cfset expression = "#Ucase(Trim(filtercondition))#" >
					
						<cfset filtervalue = CreateODBCDateTime(filtervalue) />
               			<cfif expression  EQ "LT">
	               			<cfset where = "#where##filterdatafield#  < #filtervalue#">
						<cfelseif expression EQ "GT"> 	   
							<cfset where = "#where##filterdatafield#  > #filtervalue#">
						<cfelseif expression EQ "EQ"> 	   	
							<cfset where = "#where##filterdatafield#  = #filtervalue#">
						<cfelse>
							<cfset where = "#where##filterdatafield#  = #filtervalue#">
					    </cfif>
				<cfelse>
					<!---boolean--->
					<cfif filtervalue EQ 'true' >
						<cfset filtervalue = 'Yes' >
					<cfelse>
						<cfset filtervalue = 'No' >
					</cfif>
					<cfset where = "#where##filterdatafield#  LIKE '%#filtervalue#%'" >
				</cfif>
                <cfset tmpdatafield      = filterdatafield >
			</cfloop>
            	<cfcatch>
					<!---Do nothing here since filter is not a valid JSON string--->
				</cfcatch>
            </cftry>
            
            <cfset where = "#where#)" >
			<cfset where = Replace(where, "''", "'" , "all") />
			
			<cfif trim(where) NEQ "()">
				WHERE #PreserveSingleQuotes(where)#  AND (COMPANYCODE = 'FBC' OR COMPANYCODE = '' OR COMPANYCODE IS NULL) <!---#client.companycode#--->
			<cfelse>
				WHERE (COMPANYCODE = 'FBC' OR COMPANYCODE = '' OR COMPANYCODE IS NULL) 
			</cfif> 
			
     </cfif>
	      
	  ORDER BY 
	  <!--- Order By Arguments/Contents --->
	  <cfset thecnt = 1 >
	  <cfloop array=#sort# index="sortdata">
	  	  #sortdata.property# #sortdata.direction#
	  	  <cfif thecnt EQ ArrayLen(sort) >
		  <cfelse>
	  	  	,
		  </cfif>
		  <cfset thecnt = thecnt + 1 >
	  </cfloop>
	  <!--- End Order By Arguments/Contents --->
	  	
	  OFFSET #start# ROWS
      FETCH NEXT #limit# ROWS ONLY
	  
	  </cfquery>

<cfquery name="countAll" datasource="IBOSE_GLOBAL" >
	SELECT COUNT(*) AS found_rows FROM GRGPOOL
    	<cfif trim(where) NEQ "()">
			WHERE #PreserveSingleQuotes(where)#  AND (COMPANYCODE = 'FBC' OR COMPANYCODE = '' OR COMPANYCODE IS NULL) <!---#client.companycode#--->
			<cfelse>
				WHERE (COMPANYCODE = 'FBC' OR COMPANYCODE = '' OR COMPANYCODE IS NULL) 
			</cfif> 
    ;
    
</cfquery>


	<cfset ecounter = 1 >
	<cfset resultArr = ArrayNew(1) >
	
	<cfset rootstuct = StructNew() >
	<cfset rootstuct['totalCount'] = countAll.found_rows >
	
	<!---Creates an array of structure to be converted to JSON using serializeJSON--->
	<cfloop query="qryMRF">
		<cfset tmpresult                       = StructNew() > <!---Creates new structure in every loop to be added to the array--->
		<cfset tmpresult['FIRSTNAME']      = FIRSTNAME  >
		<cfset tmpresult['LASTNAME']           = LASTNAME >
		<cfset tmpresult['MIDDLENAME']          = MIDDLENAME  >
		<cfset tmpresult['REFERENCECODE']       = REFERENCECODE  >
		<cfset tmpresult['DATEOFAPPLICATION']                = DATEOFAPPLICATION  >
		<cfset tmpresult['POSITIONFIRSTPRIORITY']    = POSITIONFIRSTPRIORITY  >
		<cfset tmpresult['POSITIONSECONDPRIORITY']  = POSITIONSECONDPRIORITY  >
		<cfset tmpresult['POSITIONTHIRDPRIORITY']        = POSITIONTHIRDPRIORITY  >
		<cfset tmpresult['EXPECTEDSALARY']      = EXPECTEDSALARY  >
		<cfset tmpresult['CURRENTSALARY']           = CURRENTSALARY  >
		<cfset tmpresult['AGE']     = AGE  >
		<cfset tmpresult['GENDER']  	   = GENDER  >
		<cfset tmpresult['CIVILSTATUS']  	   = CIVILSTATUS  >
		<cfset tmpresult['CITIZENSHIP']  	   = CITIZENSHIP  >
		<cfset tmpresult['COLLEGESCHOOL']  		   = COLLEGESCHOOL  >
		<cfset tmpresult['COLLEGECOURSE']  			   = COLLEGECOURSE  >
		<cfset tmpresult['COLLEGEISGRAD']  		   = COLLEGEISGRAD  >
		<cfset tmpresult['POSTGRADSCHOOL']     = POSTGRADSCHOOL  >
		<cfset tmpresult['POSTGRADCOURSE']    = POSTGRADCOURSE  >
		<cfset tmpresult['POSTGRADISGRAD']      = POSTGRADISGRAD  >
		<cfset tmpresult['VOCATIONALSCHOOL']  		   = VOCATIONALSCHOOL  >
		<cfset tmpresult['VOCATIONALCOURSE']          = VOCATIONALCOURSE  >
		<cfset tmpresult['VOCATIONALISGRAD']     = VOCATIONALISGRAD  >
		<cfset tmpresult['PRESENTADDRESSPOSTAL'] 		   =  PRESENTADDRESSPOSTAL >
		<cfset tmpresult['COMPANYCODE']     = COMPANYCODE  >
		
		<cfset resultArr[ecounter] = tmpresult    >
		<cfset ecounter = ecounter + 1            >
		 
	</cfloop>
	
	<cfset rootstuct['topics'] = resultArr > 
		   
	<cfreturn rootstuct />
	
</cffunction>
	


	
<cffunction name="GetMRF" ExtDirect="true">
					
	<cfargument name="page" >
	<cfargument name="start" >
	<cfargument name="limit" >
	<cfargument name="sort" >
	<cfargument name="filter" >
	<cfargument name="group" >
	<cfargument name="departmentcode" >
	
		
	<cfif departmentcode EQ "__" >
	<cfquery name="getUserDept" datasource="FBC_CBOSE" maxrows="1" >
		SELECT DEPARTMENTCODE
		  FROM CMFPA <!---client.maintable --->
		 WHERE PERSONNELIDNO = 'P9999999999' <!---client.chapa --->
	</cfquery>
		<cfif isdefined("getUserDept") >
			<cfif getUserDept.recordcount GT 0 >
				<cfset departmentcode = getUserDept.DEPARTMENTCODE />
			</cfif>
		<cfelse>
		</cfif>
	
	</cfif>
			
			
	  <cfset var qryAuthors = "" /> 
	  
	  <cfquery name="qryMRF" datasource="FBC_CBOSE">
	    SELECT  REQUISITIONNO, 
	    		RANKCODE,
	            SKILLSREQ,
	            SPECTRAINING,
	            SEX,
	            REQUISITIONEDBY,
	            REPLACEMENTREASON,
	            REPORTINGTO,
	            REPLACEMENTOF,
	            RELIGION,
	            NATUREOFJOBVAC,
	    		NATUREOFEMP,
	    		NATIONALITY,
	    		MARITALSTAT,
	    	    EFFECTIVE,
	    		AGE,
	            BRIEFDESC,
	            DATE,
	            DATEREQUISITION,
	            IFCONTRACTUAL,
	            DATETO,
	            DEGREEREQ,
	            EDUCATTAINMENT,
	    		DATENEEDED,
	            DEPARTMENTCODE,
	            DIVISIONCODE,
	            B.DESCRIPTION AS DESCRIPTION,
	            STATUS,
	            REQUIREDNO,
	            PROJECTCODE,
	            SOURCENAME,
	            POSTEDBYIBOSE,
	            POSTEDTO,
	            A.DATELASTUPDATE AS DATELASTUPDATE
	            
	     FROM  CRGPERSONELREQUEST A LEFT JOIN CLKPOSITION B ON (A.POSITIONCODE = B.POSITIONCODE)
	     	                       
	      
	 <cfset where             = "()" >
	 <cfif isdefined('query')>
	   WHERE nameofemployee LIKE '%#query#%'
	 <cfelse>
	 	 <cfset where             = " (" >
            <cfset tmpdatafield      = "" >
            <cfset tmpfilteroperator = "0" >
			
			<cftry>
			<cfset filter = deserializejson(filter) >	<!---Deserialize JSON string coz Router forgets to do the work on filter but not on sort--->
			<cfloop array=#filter# index="filterdata">
            	<cftry>
					<cfset filterdatafield = filterdata.field />
					<cfcatch>
						<cfbreak>
					</cfcatch>
				</cftry>
            
            	<cfset filterdatafield = filterdata.field />
				<cfset filterdatafield = replace(filterdatafield, "_", ".") >
				
				<cfset filtervalue     = filterdata.value />
				<cfset filtertype      = filterdata.type />
				<cfif tmpdatafield EQ "" >
                <cfset tmpdatafield = filterdatafield >	
                <cfelseif tmpdatafield NEQ filterdatafield >
                	<cfset where = "#where# ) AND ( " >
                <cfelseif tmpdatafield EQ filterdatafield >
                	<cfif tmpfilteroperator EQ 0>
                    	<cfset where = "#where# AND " >
                    <cfelse>
                    	<cfset where = "#where# OR " >
                    </cfif>
				</cfif>
                
                <cfif ucase(filtertype) EQ "STRING" >
					<cfset where = "#where##filterdatafield#  LIKE '%#filtervalue#%'" >
				<cfelseif  ucase(filtertype) EQ "NUMERIC" >
					<cfset filtercondition = filterdata.comparison >
					<cfset expression = "#Ucase(Trim(filtercondition))#" >
               			<cfif expression  EQ "LT">
						   	<cfset where = "#where##filterdatafield#  < #filtervalue#">
						<cfelseif expression EQ "GT"> 	   
							<cfset where = "#where##filterdatafield#  > #filtervalue#">
						<cfelseif expression EQ "EQ"> 	   	
							<cfset where = "#where##filterdatafield#  = #filtervalue#">
						<cfelse>
					</cfif>
				<cfelseif  ucase(filtertype) EQ "DATE" >
					<cfset filtercondition = filterdata.comparison >
					<cfset expression = "#Ucase(Trim(filtercondition))#" >
					
						<cfset filtervalue = CreateODBCDateTime(filtervalue) />
               			<cfif expression  EQ "LT">
	               			<cfset where = "#where##filterdatafield#  < #filtervalue#">
						<cfelseif expression EQ "GT"> 	   
							<cfset where = "#where##filterdatafield#  > #filtervalue#">
						<cfelseif expression EQ "EQ"> 	   	
							<cfset where = "#where##filterdatafield#  = #filtervalue#">
						<cfelse>
							<cfset where = "#where##filterdatafield#  = #filtervalue#">
					    </cfif>
				<cfelse>
					<!---boolean--->
					<cfif filtervalue EQ 'true' >
						<cfset filtervalue = 'Yes' >
					<cfelse>
						<cfset filtervalue = 'No' >
					</cfif>
					<cfset where = "#where##filterdatafield#  LIKE '%#filtervalue#%'" >
				</cfif>
                <cfset tmpdatafield      = filterdatafield >
			</cfloop>
            	<cfcatch>
					<!---Do nothing here since filter is not a valid JSON string--->
				</cfcatch>
            </cftry>
            
            <cfset where = "#where#)" >
			<cfset where = Replace(where, "''", "'" , "all") />
			
			<cfif trim(where) NEQ "()">
				WHERE #PreserveSingleQuotes(where)# AND APPROVED LIKE '%Y%' AND DEPARTMENTCODE = '#departmentcode#'
			<cfelse>
				WHERE APPROVED LIKE '%Y%' AND DEPARTMENTCODE = '#departmentcode#'
			</cfif> 
			
     </cfif>
	      
	  ORDER BY 
	  <!--- Order By Arguments/Contents --->
	  <cfset thecnt = 1 >
	  <cfloop array=#sort# index="sortdata">
	  	  #replace(sortdata.property, "_", ".")# #sortdata.direction#
	  	  <cfif thecnt EQ ArrayLen(sort) >
		  <cfelse>
	  	  	,
		  </cfif>
		  <cfset thecnt = thecnt + 1 >
	  </cfloop>
	  <!--- End Order By Arguments/Contents --->
	  	
	  OFFSET #start# ROWS
      FETCH NEXT #limit# ROWS ONLY
	  
	  </cfquery>

<cfquery name="countAll" datasource="FBC_CBOSE" >
	SELECT COUNT(*) AS found_rows FROM CRGPERSONELREQUEST A LEFT JOIN CLKPOSITION B ON (A.POSITIONCODE = B.POSITIONCODE)
    	<cfif trim(where) NEQ "()">
			<!---WHERE <cfoutput>#PreserveSingleQuotes(where)#</cfoutput>--->
			WHERE #PreserveSingleQuotes(where)# AND POSTEDBYIBOSE = 'Y' AND DEPARTMENTCODE = '#departmentcode#'
		<cfelse>
			WHERE POSTEDBYIBOSE = 'Y' AND DEPARTMENTCODE = '#departmentcode#'
		</cfif>
    
        ;
    
</cfquery>


	<cfset ecounter = 1 >
	<cfset resultArr = ArrayNew(1) >
	
	<cfset rootstuct = StructNew() >
	<cfset rootstuct['totalCount'] = countAll.found_rows >
	
	<!---Creates an array of structure to be converted to JSON using serializeJSON--->
	<cfloop query="qryMRF">
		<cfset tmpresult                       = StructNew() > <!---Creates new structure in every loop to be added to the array--->
		<cfset tmpresult['REQUISITIONNO']      = REQUISITIONNO  >
		<cfset tmpresult['RANKCODE']           = RANKCODE >
		<cfset tmpresult['SKILLSREQ']          = SKILLSREQ  >
		<cfset tmpresult['SPECTRAINING']       = SPECTRAINING  >
		<cfset tmpresult['SEX']                = SEX  >
		<cfset tmpresult['REQUISITIONEDBY']    = REQUISITIONEDBY  >
		<cfset tmpresult['REPLACEMENTREASON']  = REPLACEMENTREASON  >
		<cfset tmpresult['REPORTINGTO']        = REPORTINGTO  >
		<cfset tmpresult['REPLACEMENTOF']      = REPLACEMENTOF  >
		<cfset tmpresult['RELIGION']           = RELIGION  >
		<cfset tmpresult['NATUREOFJOBVAC']     = NATUREOFJOBVAC  >
		<cfset tmpresult['NATUREOFEMP']  	   = NATUREOFEMP  >
		<cfset tmpresult['NATIONALITY']  	   = NATIONALITY  >
		<cfset tmpresult['MARITALSTAT']  	   = MARITALSTAT  >
		<cfset tmpresult['EFFECTIVE']  		   = EFFECTIVE  >
		<cfset tmpresult['AGE']  			   = AGE  >
		<cfset tmpresult['BRIEFDESC']  		   = BRIEFDESC  >
		<cfset tmpresult['DATELASTUPDATE']     = DATELASTUPDATE  >
		<cfset tmpresult['DATEREQUISITION']    = DATEREQUISITION  >
		<cfset tmpresult['IFCONTRACTUAL']      = IFCONTRACTUAL  >
		<cfset tmpresult['DATETO']  		   = DATETO  >
		<cfset tmpresult['DEGREEREQ']          = DEGREEREQ  >
		<cfset tmpresult['EDUCATTAINMENT']     = EDUCATTAINMENT  >
		<cfset tmpresult['DATENEEDED'] 		   =  DATENEEDED >
		<cfset tmpresult['DEPARTMENTCODE']     = qryMRF.DEPARTMENTCODE  >
		<cfset tmpresult['DIVISIONCODE']  	   = DIVISIONCODE  >
		<cfset tmpresult['B_DESCRIPTION']  	   = DESCRIPTION  >
		<cfset tmpresult['STATUS']  		   = STATUS  >
		<cfset tmpresult['REQUIREDNO']  	   = REQUIREDNO  >
		<cfset tmpresult['PROJECTCODE'] 	   = PROJECTCODE  >
		<cfset tmpresult['SOURCENAME']  	   = SOURCENAME  >
		<cfset tmpresult['POSTEDBYIBOSE'] 	           = POSTEDBYIBOSE  >
		<cfset tmpresult['POSTEDTO']           = POSTEDTO  >
		
		<cfset resultArr[ecounter] = tmpresult    >
		<cfset ecounter = ecounter + 1            >
		 
	</cfloop>
	
	<cfset rootstuct['topics'] = resultArr > 
		   
	<cfreturn rootstuct />
	
</cffunction>
	
		
	

<cffunction name="GetLocalPool" ExtDirect="true">
					
	<cfargument name="page" >
	<cfargument name="start" >
	<cfargument name="limit" >
	<cfargument name="sort" >
	<cfargument name="filter" >
	<cfargument name="group" >
	<cfargument name="requisitionno" >
	
			
	  <cfset var qryAuthors = "" /> 
	  
	  <cfquery name="qryMRF" datasource="FBC_CBOSE">
	    SELECT  A.GUID AS GUID,
				A.APPLICANTNUMBER AS APPLICANTNUMBER,
				A.LASTNAME AS LASTNAME,
				A.FIRSTNAME AS FIRSTNAME,
				A.MIDDLENAME AS MIDDLENAME,
				A.APPLICATIONDATE AS APPLICATIONDATE,
				H.EMAILADDRESS AS EMAILADDRESS,
				K.DESCRIPTION AS DESCRIPTION,
				A.JOBVACANCYSOURCE AS JOBVACANCYSOURCE,
				A.RESERVED AS RESERVED,
	            H.CONTACTCELLNUMBER AS CONTACTCELLNUMBER,
	            A.SOURCE AS SOURCE,
	            G.REQUISITIONNO AS REQUISITIONNO,
	            G.DATEPRESCREEN AS DATEPRESCREEN,
	            G.DATESENDOUT AS DATESENDOUT
	            
	             
	     FROM  CMFAP A LEFT JOIN CIN21PERSONALINFO H ON (A.GUID = H.GUID)
	      			   LEFT JOIN CMFCANDIDATELISTNG G ON (G.GUID = A.GUID)
	                   LEFT JOIN CLKPOSITION K ON (K.POSITIONCODE = A.POSITIONCODE)
	     	                       
	      
	 <cfset where             = "()" >
	 <cfif isdefined('query')>
	   WHERE nameofemployee LIKE '%#query#%' <!--- reserved for grid with search plugin --->
	 <cfelse>
	 	 <cfset where             = " (" >
            <cfset tmpdatafield      = "" >
            <cfset tmpfilteroperator = "0" >
			
			<cftry>
			<cfset filter = deserializejson(filter) >	<!---Deserialize JSON string coz Router forgets to do the work on filter but not on sort--->
			<cfloop array=#filter# index="filterdata">
            	<cftry>
					<cfset filterdatafield = filterdata.field />
					<cfcatch>
						<cfbreak>
					</cfcatch>
				</cftry>
            
            	<cfset filterdatafield = filterdata.field />
				<cfset filterdatafield = replace(filterdatafield, "_", ".") >
				
				<cfset filtervalue     = filterdata.value />
				<cfset filtertype      = filterdata.type />
				<cfif tmpdatafield EQ "" >
                <cfset tmpdatafield = filterdatafield >	
                <cfelseif tmpdatafield NEQ filterdatafield >
                	<cfset where = "#where# ) AND ( " >
                <cfelseif tmpdatafield EQ filterdatafield >
                	<cfif tmpfilteroperator EQ 0>
                    	<cfset where = "#where# AND " >
                    <cfelse>
                    	<cfset where = "#where# OR " >
                    </cfif>
				</cfif>
                
                <cfif ucase(filtertype) EQ "STRING" >
					<cfset where = "#where##filterdatafield#  LIKE '%#filtervalue#%'" >
				<cfelseif  ucase(filtertype) EQ "NUMERIC" >
					<cfset filtercondition = filterdata.comparison >
					<cfset expression = "#Ucase(Trim(filtercondition))#" >
               			<cfif expression  EQ "LT">
						   	<cfset where = "#where##filterdatafield#  < #filtervalue#">
						<cfelseif expression EQ "GT"> 	   
							<cfset where = "#where##filterdatafield#  > #filtervalue#">
						<cfelseif expression EQ "EQ"> 	   	
							<cfset where = "#where##filterdatafield#  = #filtervalue#">
						<cfelse>
					</cfif>
				<cfelseif  ucase(filtertype) EQ "DATE" >
					<cfset filtercondition = filterdata.comparison >
					<cfset expression = "#Ucase(Trim(filtercondition))#" >
					
						<cfset filtervalue = CreateODBCDateTime(filtervalue) />
               			<cfif expression  EQ "LT">
	               			<cfset where = "#where##filterdatafield#  < #filtervalue#">
						<cfelseif expression EQ "GT"> 	   
							<cfset where = "#where##filterdatafield#  > #filtervalue#">
						<cfelseif expression EQ "EQ"> 	   	
							<cfset where = "#where##filterdatafield#  = #filtervalue#">
						<cfelse>
							<cfset where = "#where##filterdatafield#  = #filtervalue#">
					    </cfif>
				<cfelse>
					<!---boolean--->
					<cfif filtervalue EQ 'true' >
						<cfset filtervalue = 'Yes' >
					<cfelse>
						<cfset filtervalue = 'No' >
					</cfif>
					<cfset where = "#where##filterdatafield#  LIKE '%#filtervalue#%'" >
				</cfif>
                <cfset tmpdatafield      = filterdatafield >
			</cfloop>
            	<cfcatch>
					<!---Do nothing here since filter is not a valid JSON string--->
				</cfcatch>
            </cftry>
            
            <cfset where = "#where#)" >
			<cfset where = Replace(where, "''", "'" , "all") />
			
			
			<cfif requisitionno NEQ "__" AND trim(where) NEQ "()">
            	WHERE #PreserveSingleQuotes(where)#  AND ( G.REQUISITIONNO = '#requisitionno#' OR G.REQUISITIONNO IS NULL OR G.REQUISITIONNO = '' OR G.REQUISITIONNO = '_' )
            <cfelseif trim(where) NEQ "()">
            	WHERE #PreserveSingleQuotes(where)# 
            <cfelseif requisitionno NEQ "__" AND trim(where) EQ "()">
				WHERE G.REQUISITIONNO = '#requisitionno#' OR G.REQUISITIONNO IS NULL OR G.REQUISITIONNO = '' OR G.REQUISITIONNO = '_'
		    <cfelse>
            </cfif> 
			
     </cfif>
	      
	  ORDER BY 
	  <!--- Order By Arguments/Contents --->
	  <cfset thecnt = 1 >
	  <cfloop array=#sort# index="sortdata">
	  	  #replace(sortdata.property, "_", ".")# #sortdata.direction#
	  	  <cfif thecnt EQ ArrayLen(sort) >
		  <cfelse>
	  	  	,
		  </cfif>
		  <cfset thecnt = thecnt + 1 >
	  </cfloop>
	  <!--- End Order By Arguments/Contents --->
	  	
	  OFFSET #start# ROWS
      FETCH NEXT #limit# ROWS ONLY
	  
	  </cfquery>

<cfquery name="countAll" datasource="FBC_CBOSE" >
	SELECT COUNT(*) AS found_rows 
      FROM  CMFAP A LEFT JOIN CIN21PERSONALINFO H ON (A.GUID = H.GUID)
	      			   LEFT JOIN CMFCANDIDATELISTNG G ON (G.GUID = A.GUID)
	                   LEFT JOIN CLKPOSITION K ON (K.POSITIONCODE = A.POSITIONCODE)
	    <cfif requisitionno NEQ "__" AND trim(where) NEQ "()">
        	WHERE #PreserveSingleQuotes(where)#  AND ( G.REQUISITIONNO = '#requisitionno#' OR G.REQUISITIONNO IS NULL OR G.REQUISITIONNO = '' OR G.REQUISITIONNO = '_' )
        <cfelseif trim(where) NEQ "()">
        	WHERE #PreserveSingleQuotes(where)# 
        <cfelseif requisitionno NEQ "__" AND trim(where) EQ "()">
			WHERE G.REQUISITIONNO = '#requisitionno#' OR G.REQUISITIONNO IS NULL OR G.REQUISITIONNO = '' OR G.REQUISITIONNO = '_'
	    <cfelse>
        </cfif> 
    ;  
</cfquery>

	<cfset ecounter = 1 >
	<cfset resultArr = ArrayNew(1) >
	
	<cfset rootstuct = StructNew() >
	<cfset rootstuct['totalCount'] = countAll.found_rows >
	
	<!---Creates an array of structure to be converted to JSON using serializeJSON--->
	<cfloop query="qryMRF">
		<cfset tmpresult = StructNew() > <!---Creates new structure in every loop to be added to the array--->
		<cfset tmpresult['A_GUID']                 = GUID  >
		<cfset tmpresult['A_APPLICANTNUMBER']      = APPLICANTNUMBER >
		<cfset tmpresult['A_LASTNAME']             = LASTNAME  >
		<cfset tmpresult['A_FIRSTNAME']       	   = FIRSTNAME  >
		<cfset tmpresult['A_APPLICATIONDATE']      = APPLICATIONDATE  >
		<cfset tmpresult['H_EMAILADDRESS']    	   = EMAILADDRESS  >
		<cfset tmpresult['K_DESCRIPTION']          = DESCRIPTION  >
		<cfif RESERVED EQ 'Y' OR RESERVED EQ 1 >
			<cfset newreserved = 1 >
		<cfelse>
			<cfset newreserved = 0 >
		</cfif>	
		<cfset tmpresult['A_RESERVED']             = newreserved  >
		<cfset tmpresult['H_CONTACTCELLNUMBER']    = CONTACTCELLNUMBER  >
		<cfset tmpresult['A_SOURCE']               = SOURCE  >
		<cfset tmpresult['G_REQUISITIONNO']  	   = qryMRF.REQUISITIONNO  >
		<cfset tmpresult['G_DATEPRESCREEN']  	   = DATEPRESCREEN  >
		<cfset tmpresult['G_DATESENDOUT']  		   = DATESENDOUT  >
		
		<cfset resultArr[ecounter] = tmpresult    >
		<cfset ecounter            = ecounter + 1 >
		 
	</cfloop>
	
	<cfset rootstuct['topics'] = resultArr > 
		   
	<cfreturn rootstuct />
	
</cffunction>
	
	
	
<cffunction name      ="gridtoexcel"
			returntype="string"
			access    ="remote"
			ExtDirect ="true"
>
    
    <cftry>
    	<cfquery name="mygriddata"
				 datasource="#client.global_dsn#"
	    >
	    	SELECT  COMPANYCODE AS 'Company Code',
				    POSITIONFIRSTPRIORITY AS 'Position First Priority',
		            POSITIONSECONDPRIORITY AS 'Position Second Priority',
		            POSITIONTHIRDPRIORITY AS 'Position Third Priority',
		            EXPECTEDSALARY AS 'Expected Salary',
		            CURRENTSALARY AS 'Current Salary',
		            DATEOFAPPLICATION AS 'Date of Application',
		            REFERENCECODE AS 'Reference Code',
		            FIRSTNAME AS 'First Name',
		            LASTNAME AS 'Last Name',
		            AGE AS Age,
		            GENDER AS Gender,
		            CIVILSTATUS AS 'Civil Status',
		            CITIZENSHIP AS Citizenship,
		            COLLEGESCHOOL AS 'College School',
		            COLLEGECOURSE AS 'College Course',
		            COLLEGEISGRAD AS 'College is Graduate',
		            POSTGRADSCHOOL AS 'Post Graduate School',
		            POSTGRADCOURSE AS 'Post Graduate Course',
		            POSTGRADISGRAD AS 'Post Graduate is Graduate',
		            VOCATIONALSCHOOL AS 'Vocational School',
		            VOCATIONALCOURSE AS 'Vocational Course',
		            VOCATIONALISGRAD AS 'Vocational is Graduate',
		            PRESENTADDRESSPOSTAL AS Address
		      
		      FROM GRGPOOL 
		  ORDER BY DATEOFAPPLICATION DESC;
		</cfquery>
   
   		<cfspreadsheet  
		    action="write" 
		    filename = "#expandpath('./')#Globalpooldata.xls"
		    overwrite = "true"				  
		    query = "mygriddata" 
		>
	<cfcatch>
		<cfreturn cfcatch.detail>
	</cfcatch>
		
	</cftry>	
	
	<cfreturn "success">	
   
</cffunction>
	
	
<cffunction name      ="gridtoexcellocal"
			returntype="string"
			access    ="remote"
			ExtDirect ="true"
>
    
    <cftry>
    	<cfquery name="mygriddata"
				 datasource="#client.company_dsn#"
	    >
	    	SELECT  
				A.APPLICANTNUMBER AS APPLICANTNUMBER,
				A.LASTNAME AS LASTNAME,
				A.FIRSTNAME AS FIRSTNAME,
				A.MIDDLENAME AS MIDDLENAME,
				A.APPLICATIONDATE AS APPLICATIONDATE,
				H_EMAILADDRESS AS H_EMAILADDRESS,
				K.DESCRIPTION AS DESCRIPTION,
				A.JOBVACANCYSOURCE AS JOBVACANCYSOURCE,
				A.RESERVED AS RESERVED,
	            H.CONTACTCELLNUMBER AS CONTACTCELLNUMBER,
	            A.SOURCE AS SOURCE,
	            G.REQUISITIONNO AS REQUISITIONNO,
	            G.DATEPRESCREEN AS DATEPRESCREEN,
	            G.DATESENDOUT AS DATESENDOUT,
	            A.GUID AS GUID
	            
	             
	     FROM  CMFAP A LEFT JOIN CIN21PERSONALINFO H ON (A.GUID = H.GUID)
	      			   LEFT JOIN CMFCANDIDATELISTNG G ON (G.GUID = A.GUID)
	                   LEFT JOIN CLKPOSITION K ON (K.POSITIONCODE = A.POSITIONCODE)
	 ORDER BY A.APPLICATIONDATE DESC;
		</cfquery>
   
   		<cfspreadsheet  
		    action="write" 
		    filename = "#expandpath('./')#Localpooldata.xls"
		    overwrite = "true"				  
		    query = "mygriddata" 
		>
	<cfcatch>
		<cfreturn cfcatch.detail>
	</cfcatch>
		
	</cftry>	
	
	<cfreturn "success">	
   
</cffunction>
	
<cffunction name      ="updatenow"
			ExtDirect ="true"
>
<cfargument name="requisitionno" > 
<cfargument name="value" >
<cfargument name="field" >

<cftry>
	
<cfset tablefield      = field >
<cfset rowvalue        = value >
<cfset requisitionno   = requisitionno >


<cftry>

<cfset resultpos = FindNoCase("DATE", tablefield) />
<cfif resultpos GT 0>
<cfset rowvalue = DateFormat(left(rowvalue, 10), 'YYYY-MM-DD') >
<cfquery name="updatecrgpersonelrequet" datasource="FBC_CBOSE">

	UPDATE CRGPERSONELREQUEST
       SET #tablefield# = <cfqueryparam  cfsqltype="cf_sql_date" value="#rowvalue#">
     WHERE REQUISITIONNO = '#requisitionno#';

</cfquery>
<cfelse>
<cfquery name="updatecrgpersonelrequet" datasource="FBC_CBOSE">

	UPDATE CRGPERSONELREQUEST
       SET #tablefield# = <cfqueryparam cfsqltype="cf_sql_varchar" value="#rowvalue#">
     WHERE REQUISITIONNO = '#requisitionno#';

</cfquery>
</cfif>

<cfcatch>
<cfquery name="updatecrgpersonelrequet" datasource="FBC_CBOSE">

	UPDATE CRGPERSONELREQUEST
       SET #tablefield# = <cfqueryparam cfsqltype="cf_sql_integer" value="#rowvalue#">
     WHERE REQUISITIONNO = '#requisitionno#';

</cfquery>
</cfcatch>
</cftry>

<cfcatch>
	<cfreturn "#cfcatch.Detail# #cfcatch.Message#">
</cfcatch>
</cftry>
<cfreturn "success" >

</cffunction>

<cffunction name      ="release"
			ExtDirect ="true"
>
<cfargument name="referencecodelist" >

<cfloop list="#referencecodelist#" index="thereference" delimiters="~" > 

	
    
    <cfquery name="queryposition" datasource="#client.global_dsn#" maxrows="1">
		SELECT POSITIONFIRSTPRIORITY,
               COMPANYFIRSTPRIORITY,
               POSITIONSECONDPRIORITY,
               COMPANYSECONDPRIORITY,
               POSITIONTHIRDPRIORITY,
               COMPANYTHIRDPRIORITY
          FROM GRGPOOL
		 WHERE REFERENCECODE = '#thereference#';
	</cfquery>
    
    <cfloop query="queryposition">
    
        <cfquery name="releaseApp" datasource="#client.global_dsn#">
            UPDATE GRGPOOL
               SET COMPANYCODE            = '',
                   POSITIONFIRSTPRIORITY  = '#POSITIONSECONDPRIORITY#',
                   COMPANYFIRSTPRIORITY   = '#COMPANYSECONDPRIORITY#',
                   POSITIONSECONDPRIORITY = '#POSITIONTHIRDPRIORITY#',
                   COMPANYSECONDPRIORITY  = '#COMPANYTHIRDPRIORITY#',
                   POSITIONTHIRDPRIORITY  = '#POSITIONFIRSTPRIORITY#',
                   COMPANYTHIRDPRIORITY   = '#COMPANYFIRSTPRIORITY#'
             WHERE REFERENCECODE          = '#thereference#';
        </cfquery>
    
    </cfloop>
 
</cfloop>

<cfreturn "success" >

</cffunction>

<cffunction name ="reserve" ExtDirect ="true" >
<cfargument name="applicantnumber" >
<cfargument name="reqnum" >

<cfset msg = "success" /> 

<cfloop list="#applicantnumber#" index="appnum" delimiters="~">
	
	<cfif len(appnum) LT 3 >
		<cfcontinue>
	</cfif>

<cftry>
	
 
	
 

		<cfquery name="qryCmfap" datasource="#client.company_dsn#" maxrows="1">
			SELECT GUID,
				   RESERVED,
				   LASTNAME,
				   FIRSTNAME,
				   MIDDLENAME,
				   SALUTATION,
				   RESERVEDBY,
				   POSITIONCODE
				   
			  FROM CMFAP
			 WHERE APPLICANTNUMBER = '#appnum#';
		
		</cfquery>
		
		<cfquery name="qryinfo" datasource="#client.company_dsn#" maxrows="1">
			SELECT GUID,
				   STREETBARRIO,
				   TOWNLOCALITY,
				   CITYPROVINCE,
				   CONTACTCELLNUMBER,
				   CONTACTPAGERNO,
				   TELEPHONENUMBER,
				   EMAILADDRESS,
				   CONTACTADDRESS,
				   CONTACTTELNO,
				   PROVINCIALADDRESS,
				   PROVTELNUMBER,
				   SEX
				   
			  FROM CIN21PERSONALINFO
			 WHERE PERSONNELIDNO = '#appnum#';
		
		</cfquery>


  
             
             
             
             <cfquery name="updateCMFAP" datasource="#client.company_dsn#">
                    UPDATE CMFAP
                    SET RESERVED   = <cfqueryparam cfsqltype="cf_sql_varchar" value="Y"/>,
                        STATUSCODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="RESERVED"/>
                    WHERE APPLICANTNUMBER = '#appnum#';
             </cfquery> 
             
             <cfquery name="updateECINEXAMRESULT" datasource="#client.company_dsn#">
                UPDATE ECINEXAMRESULT
                   SET REQUISITIONNO   = <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#"/>
                 WHERE PERSONNELIDNO = '#appnum#';
             </cfquery> 
             
             <cfquery name="updateECINTERVIEWRESULT" datasource="#client.company_dsn#">
                UPDATE ECINTERVIEWRESULT
                   SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#"/>
                 WHERE PERSONNELIDNO = '#appnum#';
             </cfquery> 
             
             <cfquery name="updateECINTERVIEWRESULTFD" datasource="#client.company_dsn#">
                UPDATE ECINTERVIEWRESULTFD
                   SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#"/>
                 WHERE PERSONNELIDNO = '#appnum#';
             </cfquery> 
             
             <cfquery name="updateECINTERVIEWRESULTSD" datasource="#client.company_dsn#">
                UPDATE ECINTERVIEWRESULTSD
                   SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#"/>
                 WHERE PERSONNELIDNO = '#appnum#';
             </cfquery> 
             
             <cfquery name="updateECINTERVIEWRESULTFINAL" datasource="#client.company_dsn#">
                UPDATE ECINTERVIEWRESULTFINAL
                   SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#"/>
                 WHERE PERSONNELIDNO = '#appnum#';
             </cfquery> 
             
             <cfquery name="updateECINJOBOFFER" datasource="#client.company_dsn#">
                UPDATE ECINJOBOFFER
                   SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#"/>
                 WHERE PERSONNELIDNO = '#appnum#';
             </cfquery> 
             
             <cfquery name="updateCINPREEMPREQCHKLIST" datasource="#client.company_dsn#">
                UPDATE CINPREEMPREQCHKLIST
                   SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#"/>
                 WHERE PERSONNELIDNO = '#appnum#';
             </cfquery>
             
             <cfquery name="updateECINCONTRACT" datasource="#client.company_dsn#">
                UPDATE ECINCONTRACT
                   SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#"/>
                 WHERE PERSONNELIDNO = '#appnum#';
             </cfquery> 
        
   
        
		<cfquery name="insert_into_candidate" datasource="#client.company_dsn#">
                
                	INSERT INTO CMFCANDIDATELISTNG (  REQUISITIONNO,
                    								  GUID,
                                                      APPLICANTNUMBER,
                                                      RESERVED,
                                                      LASTNAME,
                                                      FIRSTNAME,
                                                      MIDDLENAME,
													  STREETBARRIO,
                                                      TOWNLOCALITY,
                                                      CITYPROVINCE,
													  CONTACTCELLNUMBER,
                                                      CONTACTPAGERNO,
                                                      TELEPHONENUMBER,
                                                      EMAILADDRESS,
                                                      CONTACTADDRESS,
                                                      CONTACTTELNO,
                                                      PROVINCIALADDRESS,
                                                      PROVTELNUMBER,
													  SALUTATION,
                                                      RESERVEDBY,
                                                      GENDER,
                                                      DATEPRESCREEN,
                                                      RECCREATEDBY,
                                                      RECDATECREATED,
                                                      DATELASTUPDATE,
                                                      TIMELASTUPDATE
                                                      
                                                      )
                    
                    VALUES 							( <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.GUID#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#appnum#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="Y">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.LASTNAME#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.FIRSTNAME#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.MIDDLENAME#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.STREETBARRIO#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.TOWNLOCALITY#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.CITYPROVINCE#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.CONTACTCELLNUMBER#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.CONTACTPAGERNO#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.TELEPHONENUMBER#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(qryinfo.EMAILADDRESS)#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.CONTACTADDRESS#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.CONTACTTELNO#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.PROVINCIALADDRESS#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.PROVTELNUMBER#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.SALUTATION#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.RESERVEDBY#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryinfo.SEX#">,
                                                      <cfqueryparam cfsqltype="cf_sql_date" value="#dateformat(now(), 'YYYY-MM-DD')#"/>,
                                                      <cfqueryparam cfsqltype="cf_sql_varchar" value="#client.company_dsn#">,
													  <cfqueryparam cfsqltype="cf_sql_date" value="#DateFormat(Now(), 'MM/DD/YYYY')#">,
													  <cfqueryparam cfsqltype="cf_sql_date" value="#DateFormat(Now(), 'MM/DD/YYYY')#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#TimeFormat(Now(), 'HH:MM')#">
													  
                                                      );
                    			
                    		
                    
                
                </cfquery>     
				
				 
				<cfquery name="insert_into_shortlisted" datasource="#client.company_dsn#">
                
                	INSERT INTO CMFSHORTLISTED (      REQUISITIONNO,
                    								  GUID,
                                                      APPLICANTNUMBER,
                                                      PERSONNELIDNO,
                                                      LASTNAME,
                                                      FIRSTNAME,
                                                      MIDDLENAME,
													  SALUTATION,
                                                      POSITIONCODE,
                                                      RESERVEDBY
                                                      )
                    
                    VALUES 							( <cfqueryparam cfsqltype="cf_sql_varchar" value="#reqnum#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.GUID#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#appnum#">,
                                                      <cfqueryparam cfsqltype="cf_sql_varchar" value="#appnum#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.LASTNAME#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.FIRSTNAME#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.MIDDLENAME#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.SALUTATION#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.POSITIONCODE#">,
													  <cfqueryparam cfsqltype="cf_sql_varchar" value="#qryCmfap.RESERVEDBY#">
                                                      );
                    			
                    		
                    
                
                </cfquery> 
                
                
				
				
				
                
                <cfquery name="update_reserve" datasource="#client.company_dsn#">
                    UPDATE CMFAP
                    SET RESERVED   = <cfqueryparam cfsqltype="cf_sql_varchar" value="Y"/>,
                        STATUSCODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="RESERVED"/>  
                    WHERE APPLICANTNUMBER = '#appnum#';      
                </cfquery> 
                
   
     
        
  <cfcatch>
  
  	<cfset msg = msg & ' ' & cfcatch.Detail & ' ' & cfcatch.Message />
        <cfquery name="rollback_x" datasource="#client.company_dsn#">
            DELETE CMFCANDIDATELISTNG
            WHERE APPLICANTNUMBER = '#appnum#';
        </cfquery> 
        <cfquery name="rollback_x" datasource="#client.company_dsn#">
            DELETE CMFSHORTLISTED
            WHERE APPLICANTNUMBER = '#appnum#';
        </cfquery> 
        <cfquery name="update_reserve" datasource="#client.company_dsn#">
            UPDATE CMFAP
            SET RESERVED   = <cfqueryparam cfsqltype="cf_sql_varchar" value="N"/>,
                STATUSCODE = <cfqueryparam cfsqltype="cf_sql_varchar" value=""/>  
            WHERE APPLICANTNUMBER = '#appnum#';      
        </cfquery> 
    
   </cfcatch>
        
                
</cftry>		      

</cfloop>     

<cfreturn msg >

</cffunction>


<cffunction name="unreserve" ExtDirect="true" >
<cfargument name="applicantnumber" >
 

<cfloop list="#applicantnumber#" index="appnum" delimiters="~">
	
	<cfif len(appnum) LT 3 >
		<cfcontinue>
	</cfif>


<cfquery name="deleteCMFCANDIDATELISTNG" datasource="#client.company_dsn#">
			DELETE 
			  FROM CMFCANDIDATELISTNG
			 WHERE APPLICANTNUMBER = '#appnum#';  
</cfquery> 
<cfquery name="deleteCMFSHORTLISTED" datasource="#client.company_dsn#">
			DELETE 
			  FROM CMFSHORTLISTED
	         WHERE APPLICANTNUMBER = '#appnum#';  
</cfquery> 

<cfquery name="update_reserve" datasource="#client.company_dsn#">
			UPDATE CMFAP
			   SET RESERVED = 'N', STATUSCODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="">
			 WHERE APPLICANTNUMBER = '#appnum#';   
</cfquery> 


<cfquery name="updateECINEXAMRESULT1" datasource="#client.company_dsn#">
    UPDATE ECINEXAMRESULT
       SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="">
     WHERE PERSONNELIDNO = '#appnum#';
 </cfquery> 
 
 <cfquery name="updateECINTERVIEWRESULT2" datasource="#client.company_dsn#">
    UPDATE ECINTERVIEWRESULT
       SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="">
     WHERE PERSONNELIDNO = '#appnum#';
 </cfquery> 
 
 <cfquery name="updateECINTERVIEWRESULTFD3" datasource="#client.company_dsn#">
    UPDATE ECINTERVIEWRESULTFD
       SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="">
     WHERE PERSONNELIDNO = '#appnum#';
 </cfquery> 
 
 <cfquery name="updateECINTERVIEWRESULTSD4" datasource="#client.company_dsn#">
    UPDATE ECINTERVIEWRESULTSD
       SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="">
     WHERE PERSONNELIDNO = '#appnum#';
 </cfquery> 
 
 <cfquery name="updateECINTERVIEWRESULTFINAL5" datasource="#client.company_dsn#">
    UPDATE ECINTERVIEWRESULTFINAL
       SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="">
     WHERE PERSONNELIDNO = '#appnum#';
 </cfquery> 
 
 <cfquery name="updateECINJOBOFFER6" datasource="#client.company_dsn#">
    UPDATE ECINJOBOFFER
       SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="">
     WHERE PERSONNELIDNO = '#appnum#';
 </cfquery> 
 
<cfquery name="updateCINPREEMPREQCHKLIST7" datasource="#client.company_dsn#">
    UPDATE CINPREEMPREQCHKLIST
       SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="">
     WHERE PERSONNELIDNO = '#appnum#';
 </cfquery>
 
 <cfquery name="updateECINCONTRACT8" datasource="#client.company_dsn#">
    UPDATE ECINCONTRACT
       SET REQUISITIONNO = <cfqueryparam cfsqltype="cf_sql_varchar" value="">
     WHERE PERSONNELIDNO = '#appnum#';
 </cfquery> 

</cfloop>
<cfreturn "success">
</cffunction>


<cffunction name="sendemail" ExtDirect="true" >
<cfargument name="recipient" >
<cfargument name="subject" >
<cfargument name="body" >
<cftry>
	
	
<cfset ans = findnocase("<script>", body) >
<cfif ans NEQ 0 >
	<cfset body = left(body, ans - 1) >
</cfif>

<cfmail
	from="HR"
	to="#recipient#"
	subject="#subject#" 
	type="html"
    <!---server="smtp.gmail.com"
    port="587"
    username="noreply@foodgroup.ph"
    password="n0r3plyp4ss"
    useTLS="yes"---> 
>
<cfoutput>#body#</cfoutput>
</cfmail>
<cfquery name="update_reserve4" datasource="#client.company_dsn#">
    UPDATE CMFCANDIDATELISTNG
       SET DATESENDOUT  = <cfqueryparam cfsqltype="cf_sql_date" value="#dateformat(now(), 'YYYY-MM-DD')#"/>
     WHERE EMAILADDRESS = '#trim(recipient)#';
</cfquery> 
<cfcatch>
<cfreturn cfcatch.Detail & ' ' & cfcatch.Message >
</cfcatch>
</cftry>
 
<cfreturn "success" >
 
</cffunction>

	
</cfcomponent>